IF OBJECT_ID('UpsertServerSQL','P') IS NOT NULL
   DROP PROCEDURE dbo.UpsertServerSQL
GO

------------------------------------------------------------------------------------
--  PROCEDURE NAME: dbo.UpsertServerSQL
--     DESCRIPTION: Inserts or updates a record in the ServerSQL table.
------------------------------------------------------------------------------------
--   REFERENCED BY: Upsert-ServerSQL.ps1
--    PROJECT NAME: iSQLPS
------------------------------------------------------------------------------------
--  CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.04.2010  SYoung     Initial creation
------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[UpsertServerSQL]
       @InstanceName varchar (128),
       @Environment char (1), 
       @HostName varchar (128),
       @TCPPort varchar (10),
       @ServerNetworkProtocols varchar (128),
       @Type varchar (10),                    
       @Edition varchar (128),
       @Version varchar (128),
       @ServicePack varchar (128),
       @StartupParameters varchar (512),
       @SystemDBDevice varchar (512),
       @ErrorLogLocation varchar (512),
       @Collation varchar (128),
       @MinMemory bigint,
       @MaxMemory bigint,
       @AWEEnabled bit,
       @MaxUserConnections int,
       @Active tinyint = NULL

AS

SET NOCOUNT ON

DECLARE @ERRORCODE int, 
        @ERRMSG varchar (128),
        @ServerOSID int, 
        @ClusterID int

SELECT @ServerOSID = ServerOSID FROM dbo.ServerOS WHERE HostName = @HostName

IF (@ServerOSID > 0)
   BEGIN
   IF NOT EXISTS (SELECT ServerSQLID
                    FROM dbo.[ServerSQL]
                   WHERE InstanceName = @InstanceName AND
                         ServerOSID = @ServerOSID)
      BEGIN
      INSERT INTO dbo.[ServerSQL](
             InstanceName,
             ServerOSID,
             Environment,
             TCPPort,
             ServerNetworkProtocols,
             [Type],
             Edition,
             [Version],
             ServicePack,
             StartupParameters,
             SystemDbDevice,
             ErrorLogLocation,
             Collation,
             MinMemory,
             MaxMemory,
             AWEEnabled,
             MaxUserConnections,
             CreateDate,
             UpdateDate,
             Active)
      VALUES (@InstanceName,
             @ServerOSID,
             @Environment,
             @TCPPort,
             @ServerNetworkProtocols,
             @Type,
             @Edition,
             @Version,
             @ServicePack,
             @StartupParameters,
             @SystemDbDevice,
             @ErrorLogLocation,
             @Collation,
             @MinMemory,
             @MaxMemory,
             @AWEEnabled,
             @MaxUserConnections,
             GETDATE(),
             GETDATE(),
             @Active)
   END
   ELSE
      BEGIN
      UPDATE dbo.[ServerSQL]
         SET Environment = @Environment,
             TCPPort = @TCPPort,
             ServerNetworkProtocols = @ServerNetworkProtocols,
             [Type] = @Type,
             Edition = @Edition,
             [Version] = @Version,
             ServicePack = @ServicePack,
             StartupParameters = @StartupParameters,
             SystemDbDevice = @SystemDbDevice,
             ErrorLogLocation = @ErrorLogLocation,
             Collation = @Collation,
             MinMemory = @MinMemory,
             MaxMemory = @MaxMemory,
             AWEEnabled = @AWEEnabled,
             MaxUserConnections = @MaxUserConnections,
             UpdateDate = GETDATE(),
             Active = @Active
       WHERE InstanceName = @InstanceName AND
             ServerOSID = @ServerOSID
   END
END
ELSE
   BEGIN
	 SET @ERRMSG = 'Upsert failed - ' + OBJECT_NAME(@@PROCID)
	 SET @ERRMSG = @ERRMSG + ' Server ' + @HostName + ' does not exist in ServerOS table. Please add the host first.' 
	 RAISERROR (@ERRMSG, 16, 1)
	 RETURN (-1)
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description',
                           @value = N'Inserts or updates a record in the ServerSQL table.',
                      @level0type = N'USER',
                      @level0name = N'dbo',
                      @level1type = N'PROCEDURE',
                      @level1name = N'UpsertServerSQL'
GO
